{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "from SPARQLWrapper import SPARQLWrapper, JSON\n",
    "\n",
    "endpoint_url = \"https://query.wikidata.org/sparql\"\n",
    "\n",
    "\n",
    "\n",
    "def get_results(endpoint_url, query):\n",
    "    user_agent = \"WDQS-example Python/%s.%s\" % (sys.version_info[0], sys.version_info[1])\n",
    "    # TODO adjust user agent; see https://w.wiki/CX6\n",
    "    sparql = SPARQLWrapper(endpoint_url, agent=user_agent)\n",
    "    sparql.setQuery(query)\n",
    "    sparql.setReturnFormat(JSON)\n",
    "    return sparql.query().convert()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [],
   "source": [
    "class CacheQuery(object):\n",
    "    def __init__(self, endpoint_url=\"https://query.wikidata.org/sparql\"):\n",
    "        self.endpoint_url = endpoint_url\n",
    "        self.cache = {}\n",
    "    def query(self, query):\n",
    "        if query not in self.cache:\n",
    "            user_agent = \"WDQS-example Python/%s.%s\" % (sys.version_info[0], sys.version_info[1])\n",
    "        # TODO adjust user agent; see https://w.wiki/CX6\n",
    "            sparql = SPARQLWrapper(self.endpoint_url, agent=user_agent)\n",
    "            sparql.setQuery(query)\n",
    "            sparql.setReturnFormat(JSON)\n",
    "            self.cache[query] = sparql.query().convert()\n",
    "        return self.cache[query]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [],
   "source": [
    "wiki = CacheQuery()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [],
   "source": [
    "import munch\n",
    "query = '''\n",
    "SELECT DISTINCT ?country ?countryLabel ?population (GROUP_CONCAT(DISTINCT ?langCode;separator=\", \") AS ?langCodes) \n",
    "WHERE\n",
    "{\n",
    "  ?country wdt:P31 wd:Q3624078;\n",
    "           wdt:P1082 ?population.\n",
    "  OPTIONAL {\n",
    "  ?country wdt:P37 ?lang .\n",
    "  ?lang wdt:P424 ?langCode .\n",
    "    }\n",
    "#   BIND(\"pt\" as ?desiredLang).\n",
    "#   FILTER(STRSTARTS(?langCode, ?desiredLang)).\n",
    "#   ?lang wdt:P31/wdt:P279* wd:Q1860 .\n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"en\". }\n",
    "}\n",
    "GROUP BY ?country ?countryLabel ?population\n",
    "ORDER BY DESC(?population)\n",
    "'''\n",
    "\n",
    "results = wiki.query(query)\n",
    "\n",
    "countries = []\n",
    "for x in results['results']['bindings']:\n",
    "    a = munch.Munch()\n",
    "    a.code = x['country']['value'].split('/')[-1]\n",
    "    a.label = munch.Munch()\n",
    "    a.label.en = x['countryLabel']['value']\n",
    "    a.lang_codes = x['langCodes']['value']\n",
    "    a.primary_lang = x['langCodes']['value'].split(',')[0].split('-')[0]\n",
    "    a.population = x['population']['value']\n",
    "    countries.append(a)\n",
    "countries[0].primary_lang='zh'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {},
   "outputs": [],
   "source": [
    "# countries[0].label\n",
    "countries[0].label.en = 'China'\n",
    "countries[2].label.en = 'United States'\n",
    "countries[15].label.en = 'Congo'\n",
    "countries[65].label.en = 'the Netherlands'\n",
    "countries[113].label.en = 'Denmark'\n",
    "countries[117].label.en = 'Congo'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [],
   "source": [
    "roberta_languages = ['fr', 'en', 'de', 'af', 'sq', 'am', 'ar', 'hy', 'az', 'eu', 'be', 'bn', 'bs', 'br', 'bg', 'my', 'ca', 'zh', 'hr', 'cs', 'da', 'nl', 'eo', 'et', 'fi', 'gl', 'ka', 'el', 'gu', 'ha', 'he', 'hi', 'hu', 'is', 'id', 'ga', 'it', 'ja', 'jv', 'kn', 'kk', 'km', 'ko', 'ku', 'ky', 'lo', 'la', 'lv', 'lt', 'mk', 'mg', 'ms', 'ml', 'mr', 'mn', 'ne', 'no', 'ps', 'fa', 'pl', 'pt', 'pa', 'ro', 'ru', 'gd', 'sr', 'sd', 'si', 'sk', 'sl', 'so', 'es', 'su', 'sw', 'sv', 'ta', 'te', 'th', 'tr', 'uk', 'ur', 'ug', 'uz', 'vi', 'cy', 'xh', 'yi']\n",
    "def extract_roberta(input_dict, verbose=False):\n",
    "    ret = {}\n",
    "    for x in roberta_languages:\n",
    "        if x in input_dict:\n",
    "            if x == 'zh':\n",
    "                ret['zh'] = input_dict.get('zh-cn', input_dict.get('zh'))\n",
    "            else:\n",
    "                ret[x] = input_dict[x]\n",
    "        if x not in input_dict:\n",
    "            keys = [a for a in input_dict.keys() if a.startswith(x)]\n",
    "            if keys:\n",
    "                ret[x] = input_dict[keys[0]]\n",
    "            else:\n",
    "                pass\n",
    "                if verbose:\n",
    "                    print(x)\n",
    "    return ret"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['fr', 'en', 'de', 'af', 'sq', 'am', 'ar', 'hy', 'az', 'eu', 'be', 'bn', 'bs', 'br', 'bg', 'my', 'ca', 'zh', 'hr', 'cs', 'da', 'nl', 'eo', 'et', 'fi', 'gl', 'ka', 'el', 'gu', 'ha', 'he', 'hi', 'hu', 'is', 'id', 'ga', 'it', 'ja', 'jv', 'kn', 'kk', 'km', 'ko', 'ku', 'ky', 'lo', 'la', 'lv', 'lt', 'mk', 'mg', 'ms', 'ml', 'mr', 'mn', 'ne', 'no', 'ps', 'fa', 'pl', 'pt', 'pa', 'ro', 'ru', 'gd', 'sr', 'sd', 'si', 'sk', 'sl', 'so', 'es', 'su', 'sw', 'sv', 'ta', 'te', 'th', 'tr', 'uk', 'ur', 'ug', 'uz', 'vi', 'cy', 'xh', 'yi']\n"
     ]
    }
   ],
   "source": [
    "print(roberta_languages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "People's Republic of China\n",
      "India\n",
      "United States of America\n",
      "Indonesia\n",
      "Brazil\n",
      "Pakistan\n",
      "Nigeria\n",
      "Bangladesh\n",
      "Russia\n",
      "Mexico\n",
      "Japan\n",
      "Ethiopia\n",
      "Philippines\n",
      "Egypt\n",
      "Vietnam\n",
      "Democratic Republic of the Congo\n",
      "Germany\n",
      "Turkey\n",
      "Iran\n",
      "France\n",
      "United Kingdom\n",
      "Thailand\n",
      "Italy\n",
      "South Africa\n",
      "Tanzania\n",
      "Myanmar\n",
      "South Korea\n",
      "Colombia\n",
      "Kenya\n",
      "Spain\n",
      "Argentina\n",
      "Uganda\n",
      "Ukraine\n",
      "Algeria\n",
      "Sudan\n",
      "Poland\n",
      "Iraq\n",
      "Canada\n",
      "Morocco\n",
      "Afghanistan\n",
      "Saudi Arabia\n",
      "Uzbekistan\n",
      "Malaysia\n",
      "Angola\n",
      "Mozambique\n",
      "Nepal\n",
      "Peru\n",
      "Venezuela\n",
      "Yemen\n",
      "Ghana\n",
      "Madagascar\n",
      "North Korea\n",
      "Australia\n",
      "Ivory Coast\n",
      "Cameroon\n",
      "Niger\n",
      "Sri Lanka\n",
      "Romania\n",
      "Burkina Faso\n",
      "Malawi\n",
      "Mali\n",
      "Kazakhstan\n",
      "Syria\n",
      "Chile\n",
      "Guatemala\n",
      "Kingdom of the Netherlands\n",
      "Zambia\n",
      "Ecuador\n",
      "Zimbabwe\n",
      "Cambodia\n",
      "Senegal\n",
      "Chad\n",
      "Guinea\n",
      "South Sudan\n",
      "Rwanda\n",
      "Tunisia\n",
      "Cuba\n",
      "Belgium\n",
      "Benin\n",
      "Bolivia\n",
      "Somalia\n",
      "Haiti\n",
      "Burundi\n",
      "Greece\n",
      "Czech Republic\n",
      "Portugal\n",
      "Jordan\n",
      "Dominican Republic\n",
      "Sweden\n",
      "Azerbaijan\n",
      "Hungary\n",
      "Belarus\n",
      "United Arab Emirates\n",
      "Honduras\n",
      "Israel\n",
      "Tajikistan\n",
      "Austria\n",
      "Switzerland\n",
      "Papua New Guinea\n",
      "Togo\n",
      "Sierra Leone\n",
      "Serbia\n",
      "Bulgaria\n",
      "Laos\n",
      "Paraguay\n",
      "Libya\n",
      "El Salvador\n",
      "Nicaragua\n",
      "Kyrgyzstan\n",
      "Lebanon\n",
      "Singapore\n",
      "Turkmenistan\n",
      "Denmark\n",
      "Kingdom of Denmark\n",
      "Finland\n",
      "Slovakia\n",
      "Norway\n",
      "Republic of the Congo\n",
      "New Zealand\n",
      "Costa Rica\n",
      "Oman\n",
      "Ireland\n",
      "Liberia\n",
      "Central African Republic\n",
      "Kuwait\n",
      "Mauritania\n",
      "Croatia\n",
      "Panama\n",
      "Georgia\n",
      "Bosnia and Herzegovina\n",
      "Eritrea\n",
      "Uruguay\n",
      "Mongolia\n",
      "Albania\n",
      "Armenia\n",
      "Jamaica\n",
      "Lithuania\n",
      "Qatar\n",
      "Moldova\n",
      "Namibia\n",
      "Botswana\n",
      "Lesotho\n",
      "The Gambia\n",
      "North Macedonia\n",
      "Slovenia\n",
      "Gabon\n",
      "Latvia\n",
      "Guinea-Bissau\n",
      "Bahrain\n",
      "Trinidad and Tobago\n",
      "Eswatini\n",
      "Estonia\n",
      "East Timor\n",
      "Equatorial Guinea\n",
      "Mauritius\n",
      "Cyprus\n",
      "Djibouti\n",
      "Fiji\n",
      "Comoros\n",
      "Bhutan\n",
      "Guyana\n",
      "Luxembourg\n",
      "Montenegro\n",
      "Solomon Islands\n",
      "Suriname\n",
      "Cape Verde\n",
      "Malta\n",
      "Maldives\n",
      "Brunei\n",
      "The Bahamas\n",
      "Belize\n",
      "Iceland\n",
      "Barbados\n",
      "Vanuatu\n",
      "Orange Free State\n",
      "São Tomé and Príncipe\n",
      "Samoa\n",
      "Saint Lucia\n",
      "Kiribati\n",
      "Saint Vincent and the Grenadines\n",
      "Tonga\n",
      "Grenada\n",
      "Federated States of Micronesia\n",
      "Antigua and Barbuda\n",
      "Orange Free State\n",
      "Seychelles\n",
      "Andorra\n",
      "Dominica\n",
      "Saint Kitts and Nevis\n",
      "Marshall Islands\n",
      "Monaco\n",
      "Liechtenstein\n",
      "San Marino\n",
      "Palau\n",
      "Nauru\n",
      "Tuvalu\n",
      "Vatican City\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT DISTINCT ?country ?countryLabel ?lang \n",
    "{\n",
    "  ?country rdfs:label ?countryLabel .\n",
    "  FILTER(?country in (wd:%s)).\n",
    "  BIND(lang(?countryLabel) as ?lang)\n",
    "}\n",
    "'''\n",
    "for country in countries:\n",
    "    print(country.label.en)\n",
    "    results = wiki.query(query % country.code)\n",
    "    langval = {}\n",
    "    for x in results[\"results\"][\"bindings\"]:\n",
    "#         print(x)\n",
    "        lang = x['lang']['value']\n",
    "        val = x['countryLabel']['value']\n",
    "        langval[lang]= val\n",
    "    roberta_labels = extract_roberta(langval)\n",
    "    for x, v in roberta_labels.items():\n",
    "        country.label[x] = v\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "China\n",
      "India\n",
      "United States\n",
      "Indonesia\n",
      "Brazil\n",
      "Pakistan\n",
      "Nigeria\n",
      "Bangladesh\n",
      "Russia\n",
      "Mexico\n",
      "Japan\n",
      "Ethiopia\n",
      "Philippines\n",
      "Egypt\n",
      "Vietnam\n",
      "Congo\n",
      "Germany\n",
      "Turkey\n",
      "Iran\n",
      "France\n",
      "United Kingdom\n",
      "Thailand\n",
      "Italy\n",
      "South Africa\n",
      "Tanzania\n",
      "Myanmar\n",
      "South Korea\n",
      "Colombia\n",
      "Kenya\n",
      "Spain\n",
      "Argentina\n",
      "Uganda\n",
      "Ukraine\n",
      "Algeria\n",
      "Sudan\n",
      "Poland\n",
      "Iraq\n",
      "Canada\n",
      "Morocco\n",
      "Afghanistan\n",
      "Saudi Arabia\n",
      "Uzbekistan\n",
      "Malaysia\n",
      "Angola\n",
      "Mozambique\n",
      "Nepal\n",
      "Peru\n",
      "Venezuela\n",
      "Yemen\n",
      "Ghana\n",
      "Madagascar\n",
      "North Korea\n",
      "Australia\n",
      "Ivory Coast\n",
      "Cameroon\n",
      "Niger\n",
      "Sri Lanka\n",
      "Romania\n",
      "Burkina Faso\n",
      "Malawi\n",
      "Mali\n",
      "Kazakhstan\n",
      "Syria\n",
      "Chile\n",
      "Guatemala\n",
      "the Netherlands\n",
      "Zambia\n",
      "Ecuador\n",
      "Zimbabwe\n",
      "Cambodia\n",
      "Senegal\n",
      "Chad\n",
      "Guinea\n",
      "South Sudan\n",
      "Rwanda\n",
      "Tunisia\n",
      "Cuba\n",
      "Belgium\n",
      "Benin\n",
      "Bolivia\n",
      "Somalia\n",
      "Haiti\n",
      "Burundi\n",
      "Greece\n",
      "Czech Republic\n",
      "Portugal\n",
      "Jordan\n",
      "Dominican Republic\n",
      "Sweden\n",
      "Azerbaijan\n",
      "Hungary\n",
      "Belarus\n",
      "United Arab Emirates\n",
      "Honduras\n",
      "Israel\n",
      "Tajikistan\n",
      "Austria\n",
      "Switzerland\n",
      "Papua New Guinea\n",
      "Togo\n",
      "Sierra Leone\n",
      "Serbia\n",
      "Bulgaria\n",
      "Laos\n",
      "Paraguay\n",
      "Libya\n",
      "El Salvador\n",
      "Nicaragua\n",
      "Kyrgyzstan\n",
      "Lebanon\n",
      "Singapore\n",
      "Turkmenistan\n",
      "Denmark\n",
      "Denmark\n",
      "Finland\n",
      "Slovakia\n",
      "Norway\n",
      "Congo\n",
      "New Zealand\n",
      "Costa Rica\n",
      "Oman\n",
      "Ireland\n",
      "Liberia\n",
      "Central African Republic\n",
      "Kuwait\n",
      "Mauritania\n",
      "Croatia\n",
      "Panama\n",
      "Georgia\n",
      "Bosnia and Herzegovina\n",
      "Eritrea\n",
      "Uruguay\n",
      "Mongolia\n",
      "Albania\n",
      "Armenia\n",
      "Jamaica\n",
      "Lithuania\n",
      "Qatar\n",
      "Moldova\n",
      "Namibia\n",
      "Botswana\n",
      "Lesotho\n",
      "The Gambia\n",
      "North Macedonia\n",
      "Slovenia\n",
      "Gabon\n",
      "Latvia\n",
      "Guinea-Bissau\n",
      "Bahrain\n",
      "Trinidad and Tobago\n",
      "Eswatini\n",
      "Estonia\n",
      "East Timor\n",
      "Equatorial Guinea\n",
      "Mauritius\n",
      "Cyprus\n",
      "Djibouti\n",
      "Fiji\n",
      "Comoros\n",
      "Bhutan\n",
      "Guyana\n",
      "Luxembourg\n",
      "Montenegro\n",
      "Solomon Islands\n",
      "Suriname\n",
      "Cape Verde\n",
      "Malta\n",
      "Maldives\n",
      "Brunei\n",
      "The Bahamas\n",
      "Belize\n",
      "Iceland\n",
      "Barbados\n",
      "Vanuatu\n",
      "Orange Free State\n",
      "São Tomé and Príncipe\n",
      "Samoa\n",
      "Saint Lucia\n",
      "Kiribati\n",
      "Saint Vincent and the Grenadines\n",
      "Tonga\n",
      "Grenada\n",
      "Federated States of Micronesia\n",
      "Antigua and Barbuda\n",
      "Orange Free State\n",
      "Seychelles\n",
      "Andorra\n",
      "Dominica\n",
      "Saint Kitts and Nevis\n",
      "Marshall Islands\n",
      "Monaco\n",
      "Liechtenstein\n",
      "San Marino\n",
      "Palau\n",
      "Nauru\n",
      "Tuvalu\n",
      "Vatican City\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT DISTINCT ?city ?cityLabel ?population\n",
    "{\n",
    "  ?city wdt:P31/wdt:P279* wd:Q515 .\n",
    "  ?city wdt:P1082 ?population .\n",
    "  ?city wdt:P17 ?country .\n",
    "  ?city rdfs:label ?cityLabel .\n",
    "  FILTER(lang(?cityLabel)=\"en\").\n",
    "  FILTER(?country in (wd:%s)).\n",
    "}\n",
    "ORDER BY DESC(?population) LIMIT 100\n",
    "'''\n",
    "for country in countries:\n",
    "    country.cities = []\n",
    "    results = wiki.query(query % country.code)\n",
    "    print(country.label.en)\n",
    "    for x in results[\"results\"][\"bindings\"]:\n",
    "        a = munch.Munch()\n",
    "        a.code = x['city']['value'].split('/')[-1]\n",
    "        a.label = munch.Munch()\n",
    "        a.label.en = x['cityLabel']['value']\n",
    "        a.population = x['population']['value']\n",
    "        country.cities.append(a)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "metadata": {},
   "outputs": [],
   "source": [
    "# countries[0].cities[0].label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "China\n",
      "India\n",
      "United States\n",
      "Indonesia\n",
      "Brazil\n",
      "Pakistan\n",
      "Nigeria\n",
      "Bangladesh\n",
      "Russia\n",
      "Mexico\n",
      "Japan\n",
      "Ethiopia\n",
      "Philippines\n",
      "Egypt\n",
      "Vietnam\n",
      "Congo\n",
      "Germany\n",
      "Turkey\n",
      "Iran\n",
      "France\n",
      "United Kingdom\n",
      "Thailand\n",
      "Italy\n",
      "South Africa\n",
      "Tanzania\n",
      "Myanmar\n",
      "South Korea\n",
      "Colombia\n",
      "Kenya\n",
      "Spain\n",
      "Argentina\n",
      "Uganda\n",
      "Ukraine\n",
      "Algeria\n",
      "Sudan\n",
      "Poland\n",
      "Iraq\n",
      "Canada\n",
      "Morocco\n",
      "Afghanistan\n",
      "Saudi Arabia\n",
      "Uzbekistan\n",
      "Malaysia\n",
      "Angola\n",
      "Mozambique\n",
      "Nepal\n",
      "Peru\n",
      "Venezuela\n",
      "Yemen\n",
      "Ghana\n",
      "Madagascar\n",
      "North Korea\n",
      "Australia\n",
      "Ivory Coast\n",
      "Cameroon\n",
      "Niger\n",
      "Sri Lanka\n",
      "Romania\n",
      "Burkina Faso\n",
      "Malawi\n",
      "Mali\n",
      "Kazakhstan\n",
      "Syria\n",
      "Chile\n",
      "Guatemala\n",
      "the Netherlands\n",
      "Zambia\n",
      "Ecuador\n",
      "Zimbabwe\n",
      "Cambodia\n",
      "Senegal\n",
      "Chad\n",
      "Guinea\n",
      "South Sudan\n",
      "Rwanda\n",
      "Tunisia\n",
      "Cuba\n",
      "Belgium\n",
      "Benin\n",
      "Bolivia\n",
      "Somalia\n",
      "Haiti\n",
      "Burundi\n",
      "Greece\n",
      "Czech Republic\n",
      "Portugal\n",
      "Jordan\n",
      "Dominican Republic\n",
      "Sweden\n",
      "Azerbaijan\n",
      "Hungary\n",
      "Belarus\n",
      "United Arab Emirates\n",
      "Honduras\n",
      "Israel\n",
      "Tajikistan\n",
      "Austria\n",
      "Switzerland\n",
      "Papua New Guinea\n",
      "Togo\n",
      "Sierra Leone\n",
      "Serbia\n",
      "Bulgaria\n",
      "Laos\n",
      "Paraguay\n",
      "Libya\n",
      "El Salvador\n",
      "Nicaragua\n",
      "Kyrgyzstan\n",
      "Lebanon\n",
      "Singapore\n",
      "Turkmenistan\n",
      "Denmark\n",
      "Denmark\n",
      "Finland\n",
      "Slovakia\n",
      "Norway\n",
      "Congo\n",
      "New Zealand\n",
      "Costa Rica\n",
      "Oman\n",
      "Ireland\n",
      "Liberia\n",
      "Central African Republic\n",
      "Kuwait\n",
      "Mauritania\n",
      "Croatia\n",
      "Panama\n",
      "Georgia\n",
      "Bosnia and Herzegovina\n",
      "Eritrea\n",
      "Uruguay\n",
      "Mongolia\n",
      "Albania\n",
      "Armenia\n",
      "Jamaica\n",
      "Lithuania\n",
      "Qatar\n",
      "Moldova\n",
      "Namibia\n",
      "Botswana\n",
      "Lesotho\n",
      "The Gambia\n",
      "North Macedonia\n",
      "Slovenia\n",
      "Gabon\n",
      "Latvia\n",
      "Guinea-Bissau\n",
      "Bahrain\n",
      "Trinidad and Tobago\n",
      "Eswatini\n",
      "Estonia\n",
      "East Timor\n",
      "Equatorial Guinea\n",
      "Mauritius\n",
      "Cyprus\n",
      "Djibouti\n",
      "Fiji\n",
      "Comoros\n",
      "Bhutan\n",
      "Guyana\n",
      "Luxembourg\n",
      "Montenegro\n",
      "Solomon Islands\n",
      "Suriname\n",
      "Cape Verde\n",
      "Malta\n",
      "Maldives\n",
      "Brunei\n",
      "The Bahamas\n",
      "Belize\n",
      "Iceland\n",
      "Barbados\n",
      "Vanuatu\n",
      "Orange Free State\n",
      "São Tomé and Príncipe\n",
      "Samoa\n",
      "Saint Lucia\n",
      "Kiribati\n",
      "Saint Vincent and the Grenadines\n",
      "Tonga\n",
      "Grenada\n",
      "Federated States of Micronesia\n",
      "Antigua and Barbuda\n",
      "Orange Free State\n",
      "Seychelles\n",
      "Andorra\n",
      "Dominica\n",
      "Saint Kitts and Nevis\n",
      "Marshall Islands\n",
      "Monaco\n",
      "Liechtenstein\n",
      "San Marino\n",
      "Palau\n",
      "Nauru\n",
      "Tuvalu\n",
      "Vatican City\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT DISTINCT ?city ?cityLabel ?lang \n",
    "{\n",
    "  ?city rdfs:label ?cityLabel .\n",
    "  FILTER(?city in (wd:%s)).\n",
    "  BIND(lang(?cityLabel) as ?lang)\n",
    "}\n",
    "'''\n",
    "for country in countries:\n",
    "    print(country.label.en)\n",
    "    for city in country.cities:\n",
    "        results = wiki.query(query % city.code)\n",
    "        langval = {}\n",
    "        for x in results[\"results\"][\"bindings\"]:\n",
    "            lang = x['lang']['value']\n",
    "            val = x['cityLabel']['value']\n",
    "            langval[lang]= val\n",
    "        roberta_labels = extract_roberta(langval)\n",
    "        for x, v in roberta_labels.items():\n",
    "            city.label[x] = v\n",
    "#         for x in roberta_languages:\n",
    "#             if x in langval:\n",
    "#                 if x == 'zh':\n",
    "#                     city.label['zh'] = langval.get('zh-cn', langval.get('zh'))\n",
    "#                 city.label[x] = langval[x]\n",
    "#             if x not in langval:\n",
    "#                 keys = [a for a in langval.keys() if a.startswith(x)]\n",
    "#                 if keys:\n",
    "#                     city.label[keys[0]] = langval[keys[0]]\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(countries, open('/home/marcotcr/tmp/country_stuff.pkl', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Q668'"
      ]
     },
     "execution_count": 335,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries[1].code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Q148'"
      ]
     },
     "execution_count": 433,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries[0].code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 436,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "China\n",
      "zh\n",
      "India\n",
      "hi\n",
      "United States\n",
      "en\n",
      "Indonesia\n",
      "id\n",
      "Brazil\n",
      "pt\n",
      "Pakistan\n",
      "ur\n",
      "Nigeria\n",
      "en\n",
      "Bangladesh\n",
      "bn\n",
      "Russia\n",
      "ru\n",
      "Mexico\n",
      "es\n",
      "Japan\n",
      "ja\n",
      "Ethiopia\n",
      "am\n",
      "Philippines\n",
      "en\n",
      "Egypt\n",
      "ar\n",
      "Vietnam\n",
      "vi\n",
      "Congo\n",
      "fr\n",
      "Germany\n",
      "de\n",
      "Turkey\n",
      "tr\n",
      "Iran\n",
      "fa\n",
      "France\n",
      "fr\n",
      "United Kingdom\n",
      "en\n",
      "Thailand\n",
      "th\n",
      "Italy\n",
      "it\n",
      "South Africa\n",
      "en\n",
      "Tanzania\n",
      "en\n",
      "Myanmar\n",
      "my\n",
      "South Korea\n",
      "ko\n",
      "Colombia\n",
      "es\n",
      "Kenya\n",
      "en\n",
      "Spain\n",
      "es\n",
      "Argentina\n",
      "es\n",
      "Uganda\n",
      "en\n",
      "Ukraine\n",
      "uk\n",
      "Algeria\n",
      "ar\n",
      "Sudan\n",
      "en\n",
      "Poland\n",
      "pl\n",
      "Iraq\n",
      "ar\n",
      "Canada\n",
      "fr\n",
      "Morocco\n",
      "ar\n",
      "Afghanistan\n",
      "uz\n",
      "Saudi Arabia\n",
      "ar\n",
      "Uzbekistan\n",
      "uz\n",
      "Malaysia\n",
      "ms\n",
      "Angola\n",
      "pt\n",
      "Mozambique\n",
      "pt\n",
      "Nepal\n",
      "ne\n",
      "Peru\n",
      "es\n",
      "Venezuela\n",
      "es\n",
      "Yemen\n",
      "ar\n",
      "Ghana\n",
      "en\n",
      "Madagascar\n",
      "fr\n",
      "North Korea\n",
      "ko\n",
      "Australia\n",
      "en\n",
      "Ivory Coast\n",
      "fr\n",
      "Cameroon\n",
      "fr\n",
      "Niger\n",
      "fr\n",
      "Sri Lanka\n",
      "ta\n",
      "Romania\n",
      "ro\n",
      "Burkina Faso\n",
      "fr\n",
      "Malawi\n",
      "en\n",
      "Mali\n",
      "fr\n",
      "Kazakhstan\n",
      "ru\n",
      "Syria\n",
      "ar\n",
      "Chile\n",
      "es\n",
      "Guatemala\n",
      "es\n",
      "the Netherlands\n",
      "nl\n",
      "Zambia\n",
      "en\n",
      "Ecuador\n",
      "es\n",
      "Zimbabwe\n",
      "en\n",
      "Cambodia\n",
      "km\n",
      "Senegal\n",
      "fr\n",
      "Chad\n",
      "fr\n",
      "Guinea\n",
      "fr\n",
      "South Sudan\n",
      "en\n",
      "Rwanda\n",
      "fr\n",
      "Tunisia\n",
      "ar\n",
      "Cuba\n",
      "es\n",
      "Belgium\n",
      "fr\n",
      "Benin\n",
      "fr\n",
      "Bolivia\n",
      "es\n",
      "Somalia\n",
      "so\n",
      "Haiti\n",
      "fr\n",
      "Burundi\n",
      "fr\n",
      "Greece\n",
      "el\n",
      "Czech Republic\n",
      "cs\n",
      "Portugal\n",
      "pt\n",
      "Jordan\n",
      "ar\n",
      "Dominican Republic\n",
      "es\n",
      "Sweden\n",
      "sv\n",
      "Azerbaijan\n",
      "az\n",
      "Hungary\n",
      "hu\n",
      "Belarus\n",
      "ru\n",
      "United Arab Emirates\n",
      "ar\n",
      "Honduras\n",
      "es\n",
      "Israel\n",
      "he\n",
      "Tajikistan\n",
      "ru\n",
      "Austria\n",
      "de\n",
      "Switzerland\n",
      "fr\n",
      "Papua New Guinea\n",
      "en\n",
      "Togo\n",
      "fr\n",
      "Sierra Leone\n",
      "en\n",
      "Serbia\n",
      "sr\n",
      "Bulgaria\n",
      "bg\n",
      "Laos\n",
      "lo\n",
      "Paraguay\n",
      "es\n",
      "Libya\n",
      "ar\n",
      "El Salvador\n",
      "es\n",
      "Nicaragua\n",
      "es\n",
      "Kyrgyzstan\n",
      "ru\n",
      "Lebanon\n",
      "ar\n",
      "Singapore\n",
      "en\n",
      "Turkmenistan\n",
      "tk\n",
      "Denmark\n",
      "da\n",
      "Denmark\n",
      "da\n",
      "Finland\n",
      "fi\n",
      "Slovakia\n",
      "sk\n",
      "Norway\n",
      "no\n",
      "Congo\n",
      "fr\n",
      "New Zealand\n",
      "en\n",
      "Costa Rica\n",
      "es\n",
      "Oman\n",
      "ar\n",
      "Ireland\n",
      "en\n",
      "Liberia\n",
      "en\n",
      "Central African Republic\n",
      "fr\n",
      "Kuwait\n",
      "ar\n",
      "Mauritania\n",
      "fr\n",
      "Croatia\n",
      "hr\n",
      "Panama\n",
      "es\n",
      "Georgia\n",
      "ab\n",
      "Bosnia and Herzegovina\n",
      "hr\n",
      "Eritrea\n",
      "en\n",
      "Uruguay\n",
      "es\n",
      "Mongolia\n",
      "mn\n",
      "Albania\n",
      "sq\n",
      "Armenia\n",
      "hy\n",
      "Jamaica\n",
      "en\n",
      "Lithuania\n",
      "lt\n",
      "Qatar\n",
      "ar\n",
      "Moldova\n",
      "ro\n",
      "Namibia\n",
      "en\n",
      "Botswana\n",
      "en\n",
      "Lesotho\n",
      "en\n",
      "The Gambia\n",
      "en\n",
      "North Macedonia\n",
      "sq\n",
      "Slovenia\n",
      "sl\n",
      "Gabon\n",
      "fr\n",
      "Latvia\n",
      "lv\n",
      "Guinea-Bissau\n",
      "pt\n",
      "Bahrain\n",
      "ar\n",
      "Trinidad and Tobago\n",
      "en\n",
      "Eswatini\n",
      "en\n",
      "Estonia\n",
      "et\n",
      "East Timor\n",
      "pt\n",
      "Equatorial Guinea\n",
      "fr\n",
      "Mauritius\n",
      "fr\n",
      "Cyprus\n",
      "tr\n",
      "Djibouti\n",
      "ar\n",
      "Fiji\n",
      "en\n",
      "Comoros\n",
      "fr\n",
      "Bhutan\n",
      "dz\n",
      "Guyana\n",
      "en\n",
      "Luxembourg\n",
      "fr\n",
      "Montenegro\n",
      "cnr\n",
      "Solomon Islands\n",
      "en\n",
      "Suriname\n",
      "nl\n",
      "Cape Verde\n",
      "pt\n",
      "Malta\n",
      "en\n",
      "Maldives\n",
      "dv\n",
      "Brunei\n",
      "en\n",
      "The Bahamas\n",
      "en\n",
      "Belize\n",
      "en\n",
      "Iceland\n",
      "is\n",
      "Barbados\n",
      "en\n",
      "Vanuatu\n",
      "fr\n",
      "Orange Free State\n",
      "nl\n",
      "São Tomé and Príncipe\n",
      "pt\n",
      "Samoa\n",
      "en\n",
      "Saint Lucia\n",
      "en\n",
      "Kiribati\n",
      "en\n",
      "Saint Vincent and the Grenadines\n",
      "en\n",
      "Tonga\n",
      "en\n",
      "Grenada\n",
      "en\n",
      "Federated States of Micronesia\n",
      "en\n",
      "Antigua and Barbuda\n",
      "en\n",
      "Orange Free State\n",
      "nl\n",
      "Seychelles\n",
      "fr\n",
      "Andorra\n",
      "ca\n",
      "Dominica\n",
      "en\n",
      "Saint Kitts and Nevis\n",
      "en\n",
      "Marshall Islands\n",
      "en\n",
      "Monaco\n",
      "fr\n",
      "Liechtenstein\n",
      "de\n",
      "San Marino\n",
      "it\n",
      "Palau\n",
      "en\n",
      "Nauru\n",
      "en\n",
      "Tuvalu\n",
      "en\n",
      "Vatican City\n",
      "fr\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT ?name ?nameLabel ?count\n",
    "WITH {\n",
    "  SELECT ?name ?nameLabel (count(?person) AS ?count) WHERE {\n",
    "    ?person wdt:P735 ?name .  \n",
    "    ?person wdt:P27 %s . \n",
    "    ?person wdt:P21 %s .\n",
    "  }\n",
    "  GROUP BY ?name ?nameLabel\n",
    "  ORDER BY DESC(?count)\n",
    "  LIMIT 100\n",
    "} AS %%results\n",
    "WHERE {\n",
    "  INCLUDE %%results\n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"%s,en\". }\n",
    "}\n",
    "ORDER BY DESC(?count)\n",
    "'''\n",
    "import collections\n",
    "male_names = collections.defaultdict(lambda: collections.defaultdict(lambda: 0))\n",
    "female_names = collections.defaultdict(lambda: collections.defaultdict(lambda: 0))\n",
    "male_by_country = collections.defaultdict(lambda: collections.defaultdict(lambda: 0))\n",
    "female_by_country= collections.defaultdict(lambda: collections.defaultdict(lambda: 0))\n",
    "for country in countries:\n",
    "    print(country.label.en)\n",
    "    print(country.primary_lang)\n",
    "    male = query % ('wd:%s' % country.code, 'wd:Q6581097', country.primary_lang)\n",
    "    results = wiki.query(male)\n",
    "    for x in results[\"results\"][\"bindings\"]:\n",
    "#         print(x)\n",
    "        male_names[country.primary_lang][x['nameLabel']['value']] += int(x['count']['value'])\n",
    "        male_by_country[country.label.en.replace(' ', '_')][x['nameLabel']['value']] += int(x['count']['value'])\n",
    "    female = query % ('wd:%s' % country.code, 'wd:Q6581072', country.primary_lang)\n",
    "    results = wiki.query(female)\n",
    "    for x in results[\"results\"][\"bindings\"]:\n",
    "        female_names[country.primary_lang][x['nameLabel']['value']] += int(x['count']['value'])\n",
    "        female_by_country[country.label.en.replace(' ', '_')][x['nameLabel']['value']] += int(x['count']['value'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Last names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 435,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "China\n",
      "zh\n",
      "India\n",
      "hi\n",
      "United States\n",
      "en\n",
      "Indonesia\n",
      "id\n",
      "Brazil\n",
      "pt\n",
      "Pakistan\n",
      "ur\n",
      "Nigeria\n",
      "en\n",
      "Bangladesh\n",
      "bn\n",
      "Russia\n",
      "ru\n",
      "Mexico\n",
      "es\n",
      "Japan\n",
      "ja\n",
      "Ethiopia\n",
      "am\n",
      "Philippines\n",
      "en\n",
      "Egypt\n",
      "ar\n",
      "Vietnam\n",
      "vi\n",
      "Congo\n",
      "fr\n",
      "Germany\n",
      "de\n",
      "Turkey\n",
      "tr\n",
      "Iran\n",
      "fa\n",
      "France\n",
      "fr\n",
      "United Kingdom\n",
      "en\n",
      "Thailand\n",
      "th\n",
      "Italy\n",
      "it\n",
      "South Africa\n",
      "en\n",
      "Tanzania\n",
      "en\n",
      "Myanmar\n",
      "my\n",
      "South Korea\n",
      "ko\n",
      "Colombia\n",
      "es\n",
      "Kenya\n",
      "en\n",
      "Spain\n",
      "es\n",
      "Argentina\n",
      "es\n",
      "Uganda\n",
      "en\n",
      "Ukraine\n",
      "uk\n",
      "Algeria\n",
      "ar\n",
      "Sudan\n",
      "en\n",
      "Poland\n",
      "pl\n",
      "Iraq\n",
      "ar\n",
      "Canada\n",
      "fr\n",
      "Morocco\n",
      "ar\n",
      "Afghanistan\n",
      "uz\n",
      "Saudi Arabia\n",
      "ar\n",
      "Uzbekistan\n",
      "uz\n",
      "Malaysia\n",
      "ms\n",
      "Angola\n",
      "pt\n",
      "Mozambique\n",
      "pt\n",
      "Nepal\n",
      "ne\n",
      "Peru\n",
      "es\n",
      "Venezuela\n",
      "es\n",
      "Yemen\n",
      "ar\n",
      "Ghana\n",
      "en\n",
      "Madagascar\n",
      "fr\n",
      "North Korea\n",
      "ko\n",
      "Australia\n",
      "en\n",
      "Ivory Coast\n",
      "fr\n",
      "Cameroon\n",
      "fr\n",
      "Niger\n",
      "fr\n",
      "Sri Lanka\n",
      "ta\n",
      "Romania\n",
      "ro\n",
      "Burkina Faso\n",
      "fr\n",
      "Malawi\n",
      "en\n",
      "Mali\n",
      "fr\n",
      "Kazakhstan\n",
      "ru\n",
      "Syria\n",
      "ar\n",
      "Chile\n",
      "es\n",
      "Guatemala\n",
      "es\n",
      "the Netherlands\n",
      "nl\n",
      "Zambia\n",
      "en\n",
      "Ecuador\n",
      "es\n",
      "Zimbabwe\n",
      "en\n",
      "Cambodia\n",
      "km\n",
      "Senegal\n",
      "fr\n",
      "Chad\n",
      "fr\n",
      "Guinea\n",
      "fr\n",
      "South Sudan\n",
      "en\n",
      "Rwanda\n",
      "fr\n",
      "Tunisia\n",
      "ar\n",
      "Cuba\n",
      "es\n",
      "Belgium\n",
      "fr\n",
      "Benin\n",
      "fr\n",
      "Bolivia\n",
      "es\n",
      "Somalia\n",
      "so\n",
      "Haiti\n",
      "fr\n",
      "Burundi\n",
      "fr\n",
      "Greece\n",
      "el\n",
      "Czech Republic\n",
      "cs\n",
      "Portugal\n",
      "pt\n",
      "Jordan\n",
      "ar\n",
      "Dominican Republic\n",
      "es\n",
      "Sweden\n",
      "sv\n",
      "Azerbaijan\n",
      "az\n",
      "Hungary\n",
      "hu\n",
      "Belarus\n",
      "ru\n",
      "United Arab Emirates\n",
      "ar\n",
      "Honduras\n",
      "es\n",
      "Israel\n",
      "he\n",
      "Tajikistan\n",
      "ru\n",
      "Austria\n",
      "de\n",
      "Switzerland\n",
      "fr\n",
      "Papua New Guinea\n",
      "en\n",
      "Togo\n",
      "fr\n",
      "Sierra Leone\n",
      "en\n",
      "Serbia\n",
      "sr\n",
      "Bulgaria\n",
      "bg\n",
      "Laos\n",
      "lo\n",
      "Paraguay\n",
      "es\n",
      "Libya\n",
      "ar\n",
      "El Salvador\n",
      "es\n",
      "Nicaragua\n",
      "es\n",
      "Kyrgyzstan\n",
      "ru\n",
      "Lebanon\n",
      "ar\n",
      "Singapore\n",
      "en\n",
      "Turkmenistan\n",
      "tk\n",
      "Denmark\n",
      "da\n",
      "Denmark\n",
      "da\n",
      "Finland\n",
      "fi\n",
      "Slovakia\n",
      "sk\n",
      "Norway\n",
      "no\n",
      "Congo\n",
      "fr\n",
      "New Zealand\n",
      "en\n",
      "Costa Rica\n",
      "es\n",
      "Oman\n",
      "ar\n",
      "Ireland\n",
      "en\n",
      "Liberia\n",
      "en\n",
      "Central African Republic\n",
      "fr\n",
      "Kuwait\n",
      "ar\n",
      "Mauritania\n",
      "fr\n",
      "Croatia\n",
      "hr\n",
      "Panama\n",
      "es\n",
      "Georgia\n",
      "ab\n",
      "Bosnia and Herzegovina\n",
      "hr\n",
      "Eritrea\n",
      "en\n",
      "Uruguay\n",
      "es\n",
      "Mongolia\n",
      "mn\n",
      "Albania\n",
      "sq\n",
      "Armenia\n",
      "hy\n",
      "Jamaica\n",
      "en\n",
      "Lithuania\n",
      "lt\n",
      "Qatar\n",
      "ar\n",
      "Moldova\n",
      "ro\n",
      "Namibia\n",
      "en\n",
      "Botswana\n",
      "en\n",
      "Lesotho\n",
      "en\n",
      "The Gambia\n",
      "en\n",
      "North Macedonia\n",
      "sq\n",
      "Slovenia\n",
      "sl\n",
      "Gabon\n",
      "fr\n",
      "Latvia\n",
      "lv\n",
      "Guinea-Bissau\n",
      "pt\n",
      "Bahrain\n",
      "ar\n",
      "Trinidad and Tobago\n",
      "en\n",
      "Eswatini\n",
      "en\n",
      "Estonia\n",
      "et\n",
      "East Timor\n",
      "pt\n",
      "Equatorial Guinea\n",
      "fr\n",
      "Mauritius\n",
      "fr\n",
      "Cyprus\n",
      "tr\n",
      "Djibouti\n",
      "ar\n",
      "Fiji\n",
      "en\n",
      "Comoros\n",
      "fr\n",
      "Bhutan\n",
      "dz\n",
      "Guyana\n",
      "en\n",
      "Luxembourg\n",
      "fr\n",
      "Montenegro\n",
      "cnr\n",
      "Solomon Islands\n",
      "en\n",
      "Suriname\n",
      "nl\n",
      "Cape Verde\n",
      "pt\n",
      "Malta\n",
      "en\n",
      "Maldives\n",
      "dv\n",
      "Brunei\n",
      "en\n",
      "The Bahamas\n",
      "en\n",
      "Belize\n",
      "en\n",
      "Iceland\n",
      "is\n",
      "Barbados\n",
      "en\n",
      "Vanuatu\n",
      "fr\n",
      "Orange Free State\n",
      "nl\n",
      "São Tomé and Príncipe\n",
      "pt\n",
      "Samoa\n",
      "en\n",
      "Saint Lucia\n",
      "en\n",
      "Kiribati\n",
      "en\n",
      "Saint Vincent and the Grenadines\n",
      "en\n",
      "Tonga\n",
      "en\n",
      "Grenada\n",
      "en\n",
      "Federated States of Micronesia\n",
      "en\n",
      "Antigua and Barbuda\n",
      "en\n",
      "Orange Free State\n",
      "nl\n",
      "Seychelles\n",
      "fr\n",
      "Andorra\n",
      "ca\n",
      "Dominica\n",
      "en\n",
      "Saint Kitts and Nevis\n",
      "en\n",
      "Marshall Islands\n",
      "en\n",
      "Monaco\n",
      "fr\n",
      "Liechtenstein\n",
      "de\n",
      "San Marino\n",
      "it\n",
      "Palau\n",
      "en\n",
      "Nauru\n",
      "en\n",
      "Tuvalu\n",
      "en\n",
      "Vatican City\n",
      "fr\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT ?name ?nameLabel ?count\n",
    "WITH {\n",
    "  SELECT ?name ?nameLabel (count(?person) AS ?count) WHERE {\n",
    "    ?person wdt:P734 ?name .  \n",
    "    ?person wdt:P27 %s . \n",
    "  }\n",
    "  GROUP BY ?name ?nameLabel\n",
    "  ORDER BY DESC(?count)\n",
    "  LIMIT 100\n",
    "} AS %%results\n",
    "WHERE {\n",
    "  INCLUDE %%results\n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"%s,en\". }\n",
    "}\n",
    "ORDER BY DESC(?count)\n",
    "'''\n",
    "last_names = collections.defaultdict(lambda: collections.defaultdict(lambda: 0))\n",
    "last_by_country = collections.defaultdict(lambda: collections.defaultdict(lambda: 0))\n",
    "for country in countries:\n",
    "    print(country.label.en)\n",
    "    print(country.primary_lang)\n",
    "    last = query % ('wd:%s' % country.code, country.primary_lang)\n",
    "    results = wiki.query(last)\n",
    "    for x in results[\"results\"][\"bindings\"]:\n",
    "#         print(x)\n",
    "        last_names[country.primary_lang][x['nameLabel']['value']] += int(x['count']['value'])\n",
    "        last_by_country[country.label.en.replace(' ', '_')][x['nameLabel']['value']] += int(x['count']['value'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 437,
   "metadata": {},
   "outputs": [],
   "source": [
    "mnames = extract_roberta(male_names);\n",
    "fnames = extract_roberta(female_names);\n",
    "lnames = extract_roberta(last_names)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 438,
   "metadata": {},
   "outputs": [],
   "source": [
    "# male_by_country['Brazil']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 439,
   "metadata": {},
   "outputs": [],
   "source": [
    "def valid_name(name):\n",
    "    if ('.' in name and len(name)<3) or any([x.isdigit() for x in name]):\n",
    "        print('F: %s' % name)\n",
    "        return False\n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 440,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F: K.\n",
      "F: P.\n",
      "F: S.\n",
      "F: M.\n",
      "F: A.\n",
      "F: R.\n",
      "F: V.\n",
      "F: T.\n",
      "F: N.\n",
      "F: C.\n",
      "F: B.\n",
      "F: G.\n",
      "F: D.\n",
      "F: J.\n",
      "F: H.\n",
      "F: E.\n",
      "F: O.\n",
      "F: L.\n",
      "F: U.\n",
      "F: Y.\n",
      "F: J.\n",
      "F: A.\n",
      "F: M.\n",
      "F: A.\n",
      "F: M.\n",
      "F: Q28790124\n",
      "F: A.\n",
      "F: S.\n",
      "F: B.\n",
      "F: C.\n",
      "F: A.\n",
      "F: Q66937416\n",
      "F: K.\n",
      "F: Q76885428\n",
      "F: Q66936752\n",
      "F: Q66937269\n",
      "F: Q57079574\n",
      "F: R.\n",
      "F: S.\n",
      "F: Q89128080\n",
      "F: A.\n",
      "F: M.\n",
      "F: Q60691248\n",
      "F: H.\n",
      "F: Q16628866\n",
      "F: K.\n",
      "F: S.\n",
      "F: M.\n",
      "F: V.\n",
      "F: G.\n",
      "F: P.\n",
      "F: T.\n",
      "F: A.\n",
      "F: D.\n",
      "F: B.\n",
      "F: C.\n",
      "F: E.\n",
      "F: S.\n",
      "F: M.\n",
      "F: K.\n",
      "F: A.\n",
      "F: P.\n",
      "F: C.\n",
      "F: T.\n",
      "F: D.\n",
      "F: H.\n",
      "F: V.\n",
      "F: R.\n",
      "F: J.\n",
      "F: G.\n",
      "F: W.\n",
      "F: N.\n",
      "F: E.\n",
      "F: B.\n",
      "F: U.\n",
      "F: Y.\n",
      "F: L.\n",
      "F: I.\n",
      "F: J.\n",
      "F: Q65298329\n",
      "F: Н.\n",
      "F: S.\n",
      "F: B.\n",
      "F: S.\n",
      "F: V.\n",
      "F: K.\n",
      "F: Q4247853\n",
      "F: S.\n",
      "F: E.\n",
      "F: A.\n",
      "F: Z.\n",
      "F: K.\n",
      "F: A.\n",
      "F: Q45763546\n",
      "F: Q65767684\n",
      "F: Y.\n",
      "F: E.\n",
      "F: S.\n",
      "F: J.\n",
      "F: C.\n",
      "F: R.\n",
      "F: A.\n",
      "F: E.\n",
      "F: Q64785309\n",
      "F: Q64785142\n",
      "F: A.\n",
      "F: S.\n",
      "F: C.\n",
      "F: B.\n",
      "F: D.\n",
      "F: M.\n",
      "F: S.\n",
      "F: P.\n",
      "F: B.\n",
      "F: K.\n",
      "F: R.\n",
      "F: T.\n",
      "F: V.\n",
      "F: A.\n",
      "F: C.\n",
      "F: N.\n",
      "F: Q25863245\n",
      "F: Q74550176\n",
      "F: Q69838313\n",
      "F: N.\n",
      "F: Q63115200\n",
      "F: S.\n",
      "F: Q45321546\n",
      "F: Q94579019\n",
      "F: Q66361368\n",
      "F: Q56425071\n",
      "F: F.\n",
      "F: Q58009691\n",
      "F: Q70611432\n",
      "F: Q50940212\n",
      "F: Q74421605\n",
      "F: Q55979066\n",
      "F: Q65767947\n",
      "F: Q55312707\n",
      "F: Q63144043\n",
      "F: Q67198984\n",
      "F: Q42327883\n",
      "F: Q97120444\n",
      "F: Q51159804\n",
      "F: Q47092720\n",
      "F: Q67194982\n",
      "F: Q56254960\n",
      "F: Q96105109\n",
      "F: Q59352708\n",
      "F: Q74421605\n",
      "F: Q80566758\n",
      "F: Q60239827\n",
      "F: Q65553777\n",
      "F: Q45096319\n",
      "F: Q94579019\n",
      "F: K.\n",
      "F: A.\n",
      "F: Q51272256\n",
      "F: Q88229179\n",
      "F: J.\n",
      "F: Q56692268\n",
      "F: Q62972673\n",
      "F: Q55979066\n",
      "F: Q52328234\n",
      "F: Q51545569\n",
      "F: Q64692658\n",
      "F: Q55526953\n",
      "F: Q50946340\n",
      "F: Q65767828\n",
      "F: Q63725968\n",
      "F: Q55314355\n",
      "F: B.\n",
      "F: G.\n",
      "F: Q45321546\n",
      "F: M.\n",
      "F: Q30246534\n",
      "F: A.\n",
      "F: Q69886076\n",
      "F: Q65189565\n",
      "F: Q79465858\n",
      "F: Q28914650\n",
      "F: Q51545569\n",
      "F: I.\n",
      "F: Q86685520\n",
      "F: Q94368759\n",
      "F: Z.\n",
      "F: Q97671765\n",
      "F: Q52358987\n",
      "F: Q48800342\n",
      "F: Q28871190\n",
      "F: Q43256664\n",
      "F: M.\n",
      "F: Q65189565\n",
      "F: Q97671765\n",
      "F: Q64657910\n",
      "F: Q19515301\n",
      "F: Q30080971\n",
      "F: C.\n",
      "F: R.\n",
      "F: Q65532156\n",
      "F: Q60239827\n",
      "F: Q67190715\n",
      "F: Q55400679\n",
      "F: Q52938034\n",
      "F: Q47454623\n",
      "F: Q57079566\n",
      "F: Q60023152\n",
      "F: Q95950131\n",
      "F: Q63928407\n",
      "F: Q63136026\n",
      "F: Q97036146\n",
      "F: Q56462637\n",
      "F: Q56244878\n",
      "F: Q18921304\n",
      "F: Q21125525\n",
      "F: Q24955672\n",
      "F: Q21125825\n",
      "F: Q47454428\n",
      "F: Q21126461\n",
      "F: Q21126527\n",
      "F: Q24953884\n",
      "F: Q28947845\n",
      "F: Q24955550\n",
      "F: Q18437444\n",
      "F: Q47454473\n",
      "F: Q16072112\n",
      "F: Q21180656\n",
      "F: Q21993849\n",
      "F: Q64829493\n",
      "F: Q84265651\n",
      "F: Q65562292\n",
      "F: Q37259498\n",
      "F: Q21125617\n",
      "F: Q24957485\n",
      "F: Q21126235\n",
      "F: Q24958361\n",
      "F: t1816212262\n",
      "F: Q47454474\n",
      "F: Q21125738\n",
      "F: Q47454323\n",
      "F: Q47454500\n",
      "F: Q47454379\n",
      "F: Q24954346\n",
      "F: Q24953148\n",
      "F: Q21125875\n",
      "F: Q24958232\n",
      "F: Q24953071\n",
      "F: Q16286891\n",
      "F: t1038705078\n",
      "F: t2001604864\n",
      "F: t1661779107\n",
      "F: Q24957913\n",
      "F: Q56810095\n",
      "F: Q12862919\n",
      "F: Q56798209\n",
      "F: Q24955237\n",
      "F: Q24958339\n",
      "F: Q24954083\n",
      "F: Q47454288\n",
      "F: Q72739323\n",
      "F: Q60228150\n",
      "F: Q24958283\n",
      "F: Q21125589\n",
      "F: Q63028885\n",
      "F: Q56451835\n",
      "F: Q24957840\n",
      "F: Q47454677\n",
      "F: Q64764873\n",
      "F: Q47454677\n",
      "F: Q60791688\n",
      "F: Q60793620\n",
      "F: Q21126712\n",
      "F: Q47454442\n",
      "F: Q24957458\n",
      "F: Q83791678\n",
      "F: Q57955377\n",
      "F: Q38370520\n",
      "F: Q15850851\n",
      "F: Q56119214\n",
      "F: Q47454719\n",
      "F: Q64759752\n"
     ]
    }
   ],
   "source": [
    "for country in male_by_country:\n",
    "    male_by_country[country] =  [a[0] for a in sorted(male_by_country[country].items(), key=lambda x:-x[1]) if valid_name(a[0])][:100]\n",
    "for country in female_by_country:\n",
    "    female_by_country[country] =  [a[0] for a in sorted(female_by_country[country].items(), key=lambda x:-x[1]) if valid_name(a[0])][:100]\n",
    "for country in last_by_country:\n",
    "    last_by_country[country] =  [a[0] for a in sorted(last_by_country[country].items(), key=lambda x:-x[1]) if valid_name(a[0])][:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 441,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F: J.\n",
      "F: Y.\n",
      "F: E.\n",
      "F: D.\n",
      "F: J.\n",
      "F: S.\n",
      "F: A.\n",
      "F: E.\n",
      "F: V.\n",
      "F: K.\n",
      "F: B.\n",
      "F: C.\n",
      "F: Q60691248\n",
      "F: Z.\n",
      "F: Q65767684\n",
      "F: A.\n",
      "F: S.\n",
      "F: B.\n",
      "F: C.\n",
      "F: M.\n",
      "F: R.\n",
      "F: K.\n",
      "F: P.\n",
      "F: S.\n",
      "F: M.\n",
      "F: A.\n",
      "F: R.\n",
      "F: V.\n",
      "F: T.\n",
      "F: N.\n",
      "F: C.\n",
      "F: B.\n",
      "F: G.\n",
      "F: D.\n",
      "F: J.\n",
      "F: H.\n",
      "F: E.\n",
      "F: O.\n",
      "F: L.\n",
      "F: U.\n",
      "F: Y.\n",
      "F: A.\n",
      "F: M.\n",
      "F: Q45763546\n",
      "F: K.\n",
      "F: S.\n",
      "F: M.\n",
      "F: V.\n",
      "F: G.\n",
      "F: P.\n",
      "F: T.\n",
      "F: A.\n",
      "F: D.\n",
      "F: B.\n",
      "F: C.\n",
      "F: S.\n",
      "F: E.\n",
      "F: Q64785309\n",
      "F: Q64785142\n",
      "F: Q65298329\n",
      "F: Н.\n",
      "F: S.\n",
      "F: M.\n",
      "F: K.\n",
      "F: A.\n",
      "F: P.\n",
      "F: C.\n",
      "F: T.\n",
      "F: D.\n",
      "F: H.\n",
      "F: V.\n",
      "F: R.\n",
      "F: J.\n",
      "F: G.\n",
      "F: W.\n",
      "F: N.\n",
      "F: E.\n",
      "F: B.\n",
      "F: U.\n",
      "F: Y.\n",
      "F: L.\n",
      "F: I.\n",
      "F: Q57079574\n",
      "F: R.\n",
      "F: S.\n",
      "F: Q89128080\n",
      "F: A.\n",
      "F: M.\n",
      "F: Q28790124\n",
      "F: H.\n",
      "F: Q16628866\n",
      "F: A.\n",
      "F: Q66937416\n",
      "F: K.\n",
      "F: Q76885428\n",
      "F: Q66936752\n",
      "F: Q66937269\n",
      "F: Q94579019\n",
      "F: Q47092720\n",
      "F: Q51272256\n",
      "F: Q88229179\n",
      "F: J.\n",
      "F: Q69886076\n",
      "F: Q94368759\n",
      "F: Z.\n",
      "F: M.\n",
      "F: Q64657910\n",
      "F: C.\n",
      "F: Q65189565\n",
      "F: Q48800342\n",
      "F: Q74550176\n",
      "F: Q69838313\n",
      "F: N.\n",
      "F: Q63115200\n",
      "F: S.\n",
      "F: Q55979066\n",
      "F: Q62972673\n",
      "F: Q50940212\n",
      "F: Q74421605\n",
      "F: Q51545569\n",
      "F: Q45321546\n",
      "F: Q52328234\n",
      "F: Q64692658\n",
      "F: Q65767947\n",
      "F: Q55312707\n",
      "F: Q55526953\n",
      "F: Q50946340\n",
      "F: Q63144043\n",
      "F: Q67198984\n",
      "F: Q42327883\n",
      "F: Q56254960\n",
      "F: Q96105109\n",
      "F: Q65767828\n",
      "F: Q63725968\n",
      "F: Q55314355\n",
      "F: Q97120444\n",
      "F: Q51159804\n",
      "F: Q67194982\n",
      "F: Q59352708\n",
      "F: Q28871190\n",
      "F: Q30246534\n",
      "F: Q25863245\n",
      "F: Q79465858\n",
      "F: Q28914650\n",
      "F: R.\n",
      "F: M.\n",
      "F: S.\n",
      "F: P.\n",
      "F: B.\n",
      "F: K.\n",
      "F: R.\n",
      "F: T.\n",
      "F: V.\n",
      "F: A.\n",
      "F: C.\n",
      "F: N.\n",
      "F: Q56692268\n",
      "F: Q97671765\n",
      "F: Q66361368\n",
      "F: Q56425071\n",
      "F: F.\n",
      "F: Q58009691\n",
      "F: Q60239827\n",
      "F: Q65553777\n",
      "F: Q45096319\n",
      "F: Q94579019\n",
      "F: Q65532156\n",
      "F: Q67190715\n",
      "F: Q43256664\n",
      "F: Q86685520\n",
      "F: Q65189565\n",
      "F: M.\n",
      "F: B.\n",
      "F: G.\n",
      "F: A.\n",
      "F: I.\n",
      "F: Q52358987\n",
      "F: K.\n",
      "F: A.\n",
      "F: Q70611432\n",
      "F: Q19515301\n",
      "F: Q30080971\n",
      "F: Q80566758\n",
      "F: Q21993849\n",
      "F: Q55400679\n",
      "F: Q47454473\n",
      "F: Q95950131\n",
      "F: Q21180656\n",
      "F: t2001604864\n",
      "F: Q60228150\n",
      "F: Q57955377\n",
      "F: Q47454719\n",
      "F: Q24958283\n",
      "F: Q21125589\n",
      "F: Q63028885\n",
      "F: Q56451835\n",
      "F: Q24957840\n",
      "F: Q64829493\n",
      "F: Q84265651\n",
      "F: Q60023152\n",
      "F: Q63928407\n",
      "F: Q63136026\n",
      "F: Q18921304\n",
      "F: Q16072112\n",
      "F: t1661779107\n",
      "F: Q64764873\n",
      "F: Q24955237\n",
      "F: Q24958339\n",
      "F: Q24954083\n",
      "F: Q37259498\n",
      "F: Q21125617\n",
      "F: Q24957485\n",
      "F: Q21126235\n",
      "F: Q24958361\n",
      "F: Q56798209\n",
      "F: t1038705078\n",
      "F: Q38370520\n",
      "F: Q15850851\n",
      "F: Q64759752\n",
      "F: Q47454677\n",
      "F: Q83791678\n",
      "F: Q56119214\n",
      "F: Q47454288\n",
      "F: Q72739323\n",
      "F: Q65562292\n",
      "F: Q47454474\n",
      "F: t1816212262\n",
      "F: Q47454623\n",
      "F: Q57079566\n",
      "F: Q60791688\n",
      "F: Q60793620\n",
      "F: Q21125525\n",
      "F: Q24955672\n",
      "F: Q97036146\n",
      "F: Q56462637\n",
      "F: Q56244878\n",
      "F: Q21125825\n",
      "F: Q47454428\n",
      "F: Q21126461\n",
      "F: Q21126527\n",
      "F: Q24953884\n",
      "F: Q28947845\n",
      "F: Q24955550\n",
      "F: Q18437444\n",
      "F: Q52938034\n"
     ]
    }
   ],
   "source": [
    "for lang in mnames:\n",
    "    mnames[lang] =  [a[0] for a in sorted(mnames[lang].items(), key=lambda x:-x[1]) if valid_name(a[0])][:100]\n",
    "for lang in fnames:\n",
    "    fnames[lang] =  [a[0] for a in sorted(fnames[lang].items(), key=lambda x:-x[1]) if valid_name(a[0])][:100]\n",
    "for lang in lnames:\n",
    "    lnames[lang] =  [a[0] for a in sorted(lnames[lang].items(), key=lambda x:-x[1]) if valid_name(a[0])][:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 442,
   "metadata": {},
   "outputs": [],
   "source": [
    "male_by_country = munch.Munch(dict(male_by_country))\n",
    "female_by_country = munch.Munch(dict(female_by_country))\n",
    "last_by_country = munch.Munch(dict(last_by_country))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 443,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = munch.Munch({'countries': countries,\n",
    "                   'mnames': mnames,\n",
    "                   'fnames': fnames,\n",
    "                    'lnames': lnames,\n",
    "                   'male_by_country': male_by_country,\n",
    "                   'last_by_country': last_by_country,\n",
    "                   'female_by_country': female_by_country})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 444,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "pickle.dump(data, open('/home/marcotcr/tmp/country_stuff.pkl', 'wb'))\n",
    "pickle.dump(wiki, open('/home/marcotcr/tmp/wikidata_queries.pkl', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "checklist",
   "language": "python",
   "name": "checklist"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
